package com.zxj.geekstudy.spring.db;

import com.mysql.jdbc.StringUtils;
import com.zxj.geekstudy.spring.db.po.Student;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 10.（必做）研究一下 JDBC 接口和数据库连接池，掌握它们的设计和用法：
 * 1）使用 JDBC 原生接口，实现数据库的增删改查操作。
 */
public class StatementDB {

    public static Statement statement;

    static {
        try {
            statement = initStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Statement initStatement() throws Exception {
        // 1.注册数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2.与数据库建立连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/geek_study", "root", "root");
        return connection.createStatement();
    }

    public static void closeResource() throws Exception {
        Connection connection = statement.getConnection();
        statement.close();
        connection.close();
    }

    public int insert(String name, BigDecimal score) throws Exception {
        String sql = "INSERT INTO `student` (`name`, `score`) VALUES ( '"+ name + "', '" + score + "')";
        return statement.executeUpdate(sql);
    }

    public int update(String name, BigDecimal score) throws Exception {
        if (StringUtils.isEmptyOrWhitespaceOnly(name) || score == null) {
            return 0;
        }
        String sql = "UPDATE `student` SET `score`=" + score;
        sql += " WHERE (`name`='" + name + "')";
        return statement.executeUpdate(sql);
    }

    public int delete(String name) throws Exception {
        if (StringUtils.isEmptyOrWhitespaceOnly(name)) {
            return 0;
        }
        String sql = "DELETE FROM `student` WHERE (`name`='" + name + "')";
        return statement.executeUpdate(sql);
    }

    public List<Student> query(String name) throws Exception {
        String sql = "SELECT * FROM `student`";
        if (!StringUtils.isEmptyOrWhitespaceOnly(name)) {
            sql += " WHERE name = '" + name + "'";
        }
        ResultSet resultSet = statement.executeQuery(sql);
        List<Student> list = new ArrayList<>();
        // 5.处理结果集（针对查询）
        while (resultSet.next()) {
            // 一次循环处理一行，一行转换成一个对象
            Student student = new Student(resultSet.getInt(1), resultSet.getString(2), resultSet.getBigDecimal(3));
            list.add(student);// 添加到集合
        }
        resultSet.close();
        return list;
    }

    public static void main(String[] args) throws Exception {
        StatementDB statementDB = new StatementDB();
        statementDB.insert("stu1", new BigDecimal(10));
        statementDB.insert("stu2", new BigDecimal(20));
        statementDB.insert("stu3", new BigDecimal(30));
        System.out.println(statementDB.query("stu1"));
        System.out.println(statementDB.query(null));
        statementDB.update("stu1", new BigDecimal(100));
        statementDB.delete("stu2");
        System.out.println(statementDB.query(null));
        // 关闭资源
        closeResource();
    }

}
